import datetime
import os
from decimal import Decimal

import pymysql
from PySide6 import QtCore
from PySide6.QtCore import QUrl, QPoint, QFile, QByteArray
from PySide6.QtGui import Qt, QKeySequence, QPalette
from PySide6.QtWebChannel import QWebChannel
from PySide6.QtWebEngineWidgets import QWebEngineView
from PySide6.QtWidgets import QMainWindow, QMessageBox, QMenu, QWidget, QTabWidget, QSizePolicy, QTableWidget, \
    QHeaderView, QAbstractItemView, QTableWidgetItem

from global_variables import GlobalVariables
from util import common
from util.python_js import JsPython

SQL_QUERY_DEBUG = False
DEBUG_PORT = '5588'
DEBUG_URL = 'http://127.0.0.1:%s' % DEBUG_PORT
if SQL_QUERY_DEBUG:
    os.environ['QTWEBENGINE_REMOTE_DEBUGGING'] = DEBUG_PORT


class SqlQuery(QMainWindow):
    def __init__(self, app):
        super().__init__()
        self.app = app
        self.ui = app.ui

        self.init()

    def init(self):
        self.ui.run_sql_query_button.clicked.connect(self.run_sql_query_button_clicked)
        self.ui.format_sql_query_button.clicked.connect(self.format_sql_query_button_clicked)

        # webview与python交互
        self.web_view_js_channel()

    def web_view_js_channel(self):
        self.add_web_view()
        web: QWebEngineView = self.ui.sql_text
        web.load(QUrl.fromLocalFile(GlobalVariables.PROJECT_BASE_PATH.replace(os.sep, '/') + 'sql-editor/index.html'))
        # 右键菜单
        web.setContextMenuPolicy(Qt.ContextMenuPolicy.CustomContextMenu)
        web.customContextMenuRequested.connect(lambda p: self.show_menu(p, web))

        self.app.jsChannel = channel = QWebChannel()
        self.app.jsHandler = handler = JsPython(self.app, web)
        channel.registerObject('py', handler)
        web.page().setWebChannel(channel)
        self.handler = handler

        if SQL_QUERY_DEBUG:
            # 添加调试
            view: QWebEngineView = QWebEngineView(self.ui.groupBox_sql_editor)
            view.setWindowTitle('Web Inspector')
            view.load(QUrl(DEBUG_URL))
            view.setFixedHeight(0)
            view.setFixedWidth(0)
            web.loadFinished.connect(lambda ok: self.handle_html_loaded(ok, web, view))

    def add_web_view(self):
        self.ui.sql_text = QWebEngineView(self.ui.groupBox_sql_editor)
        self.ui.sql_text.setObjectName(u"sql_text")
        sizePolicy2 = QSizePolicy(QSizePolicy.Preferred, QSizePolicy.Preferred)
        sizePolicy2.setHorizontalStretch(0)
        sizePolicy2.setVerticalStretch(0)
        sizePolicy2.setHeightForWidth(self.ui.sql_text.sizePolicy().hasHeightForWidth())
        self.ui.sql_text.setSizePolicy(sizePolicy2)
        self.ui.horizontalLayout_7.addWidget(self.ui.sql_text)

    def show_menu(self, point: QPoint, element: QWebEngineView):
        menu = QMenu(element)
        common.add_action(menu, '运行', self.run_sql_query_button_clicked)
        common.add_action(menu, '美化', self.format_sql_query_button_clicked)
        desk_point = self.ui.sql_text.mapToGlobal(point)
        menu.exec_(desk_point)

    # 加载完成后显示调试网页
    def handle_html_loaded(self, ok: bool, page: QWebEngineView, debug_page: QWebEngineView):
        if ok:
            page.page().setDevToolsPage(debug_page.page())
            debug_page.show()

    # SQL语句点击格式化
    def format_sql_query_button_clicked(self):
        self.handler.python_format_sql()

    def exec_sql_query(self, sql_text: str = ''):
        try:
            if sql_text.upper().startswith('SELECT') \
                    or sql_text.upper().startswith('EXPLAIN') \
                    or sql_text.upper().startswith('SHOW'):
                cursor = self.app.mysqldb.cursor_execute(sql_text)
                data = cursor.fetchall()
                if len(data) == 0:
                    columns = ['no content']
                else:
                    columns = list(data[0].keys())
                GlobalVariables.DATA_LIST_UNIQUE_FIELD = None
                self.data_list_fill_data_v2(obj=self.ui.data_list_sql,
                                                                    columns=columns,
                                                                    data=data)
            elif sql_text.upper().startswith('CREATE TABLE'):
                cursor = self.app.mysqldb.cursor_execute(sql_text)
                self.update_data_list_fill_data(sql_text, cursor.rowcount)
            else:
                cursor = self.app.mysqldb.cursor_execute(sql_text)
                self.update_data_list_fill_data(sql_text, cursor.rowcount)
                print(cursor.fetchall())
        except pymysql.err.OperationalError as e:
            code, msg = e.args
            QMessageBox.critical(self, '错误', msg)
        except pymysql.err.ProgrammingError as e:
            code, msg = e.args
            QMessageBox.critical(self, '错误', msg)

    def update_data_list_fill_data(self, query: str, row_number: int):
        GlobalVariables.DATA_LIST_UNIQUE_FIELD = None
        columns = ["execute query", "affect-rows"]
        data = [
            {
                "execute query": query,
                "affect-rows": row_number
            }
        ]
        self.data_list_fill_data_v2(obj=self.ui.data_list_sql,
                                    columns=columns,
                                    data=data)

    def run_sql_query(self, sql_text: str = ''):
        sql_text = sql_text.strip().replace('\u2029', '')
        sql_list = [sql for sql in sql_text.split(";") if len(sql) > 0]
        if len(sql_list) == 0:
            return
        for sql in sql_list:
            self.exec_sql_query(sql)

    # SQL语句点击查询
    def run_sql_query_button_clicked(self):
        self.handler.python_get_sql()

    def data_list_fill_data_v2(self, obj: QTableWidget, columns: list, data: list):
        # 清空数据表
        obj.clearContents()
        obj.setRowCount(0)
        GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP = {}
        unique_field = GlobalVariables.DATA_LIST_UNIQUE_FIELD

        # 设置隔行变色
        obj.setAlternatingRowColors(True)
        obj.setPalette(QPalette(QtCore.Qt.GlobalColor.gray))

        # 设置列数
        obj.setColumnCount(len(columns))
        # 设置自适应宽度
        obj.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
        # 设置列最大宽度
        obj.horizontalHeader().setMaximumSectionSize(200)
        # 设置列最小宽度
        obj.horizontalHeader().setMinimumSectionSize(30)
        # 设置表格为整行选择
        obj.setSelectionBehavior(QAbstractItemView.SelectRows)

        map_k_column = {}
        for k in range(len(columns)):
            map_k_column[columns[k]] = k
            obj.setHorizontalHeaderItem(k, QTableWidgetItem(columns[k]))

        GlobalVariables.DATA_LIST_IS_INSERT = True
        for d in data:
            # print(d)
            self.add_item(obj, unique_field, d, map_k_column)
        GlobalVariables.DATA_LIST_IS_INSERT = False

    # 添加单元格
    def add_item(self, obj, unique_field, d, map_k_column):
        row_count = obj.rowCount() + 1
        obj.setRowCount(row_count)
        if unique_field is not None:
            GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP[row_count - 1] = d[unique_field]
        for column in map_k_column:
            if isinstance(d[column], float):
                content = str(d[column])
            if isinstance(d[column], int):
                content = str(d[column])
            elif isinstance(d[column], str):
                content = d[column]
            elif d[column] is None:
                content = 'NULL'
            elif isinstance(d[column], bytes):
                content = d[column].decode('utf-8')
            elif isinstance(d[column], Decimal):
                content = str(d[column])
            elif isinstance(d[column], datetime.datetime):
                content = d[column].strftime("%Y-%m-%d %H:%M:%S")
            else:
                content = ''
                print(d[column], type(d[column]), map_k_column[column])
            obj.setItem(row_count - 1, map_k_column[column], QTableWidgetItem(content))

    def sync_table_and_column(self):
        self.handler.set_all_table_and_column()
